﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DAL;
using DTO;

namespace BLL
{
    public class PhanGVBLL
    {
        static int id = 0;
        QuanLyDiemDataContext DB = new QuanLyDiemDataContext();
        public List<ToDTO> getTo()
        {
            //lay het du lieu tu bang khoi
            List<To> list = DB.Tos.ToList<To>();
            //tao moi 1 doi tuong KhoiDTO de chua du lieu cua bang khoi
            List<ToDTO> listdto = new List<ToDTO>();
            foreach (To lop in list)
            {
                ToDTO k = new ToDTO();
                k.Mato = lop.MaTo;
                k.Tento = lop.TenTo;
                listdto.Add(k);
            }
            return listdto;
        }
        public IQueryable DanhSachGV(string maphanlop, string matkbkhoi,string mato)
        {
            return DB.CanBos.Join(
                DB.GiaoViens,
                a=>a.MaCanBo,
                b=>b.MaCanBo,
                (a,b)=> new
                {
                    a.MaCanBo,
                    a.HoTen,
                    b.MaTo
                }
                ).Where(s =>s.MaTo==mato && !(from p in DB.PhanGVs where p.MaPhanLop == maphanlop && p.MaTKBKhoi==matkbkhoi select p.MaCanBo).Contains(s.MaCanBo)).AsQueryable();
        }
        public List<NamHocDTO> getNamHocChuaKetThuc()
        {
            //lay het du lieu tu bang khoi
            List<NamHoc> list = DB.NamHocs.Where(s=>s.TrangThai=="Chưa kết thúc").ToList<NamHoc>();
            //tao moi 1 doi tuong KhoiDTO de chua du lieu cua bang khoi
            List<NamHocDTO> listdto = new List<NamHocDTO>();
            foreach (NamHoc lop in list)
            {
                NamHocDTO k = new NamHocDTO();
                k.MaNamHoc = lop.MaNamHoc;
                k.TenNamHoc = lop.TenNamHoc;
                listdto.Add(k);
            }
            return listdto;
        }
        public List<LopDTO> getLopDaPhanLop(string manamhoc,string makhoi)
        {
            //lay het du lieu tu bang khoi
            var list= DB.Lops.Join(
                DB.PhanLops,
                a => a.MaLop,
                a => a.MaLop,
                (a, b) => new
                {
                    a.MaLop,
                    a.TenLop,
                    a.MaKhoi,
                    b.MaNamHoc
                }
                ).Where(s => s.MaNamHoc == manamhoc && s.MaKhoi==makhoi).AsQueryable();
            //tao moi 1 doi tuong KhoiDTO de chua du lieu cua bang khoi
            List<LopDTO> listdto = new List<LopDTO>();
            foreach (var lop in list)
            {
                LopDTO k = new LopDTO();
                k.Malop = lop.MaLop;
                k.Tenlop = lop.TenLop;
                listdto.Add(k);
            }
            return listdto;
        }
        public List<MonDTO> getMonHocCuaKhoi(string manamhoc,string makhoi)
        {
            //lay het du lieu tu bang khoi
            var list = DB.MonHocs.Join(
                DB.TKBKhois,
                a => a.MaMonHoc,
                a => a.MaMonHoc,
                (a, b) => new
                {
                    a.MaMonHoc,
                    a.TenMon,
                    b.MaKhoi,
                    b.MaNamHoc
                }
                ).Where(s => s.MaNamHoc == manamhoc && s.MaKhoi == makhoi).AsQueryable();
            //tao moi 1 doi tuong KhoiDTO de chua du lieu cua bang khoi
            List<MonDTO> listdto = new List<MonDTO>();
            foreach (var lop in list)
            {
                MonDTO k = new MonDTO();
                k.MaMonHoc = lop.MaMonHoc;
                k.TenMonHoc = lop.TenMon;
                listdto.Add(k);
            }
            return listdto;
        }
        public string getMaphanLop(string manamhoc, string malop)
        {
            return DB.PhanLops.Where(s => s.MaNamHoc == manamhoc && s.MaLop == malop).Select(s => s.MaPhanLop).FirstOrDefault().ToString();
        }
        public string getMaTKBKhoi(string makhoi, string mamon, string namhoc)
        {
            return DB.TKBKhois.Where(s => s.MaMonHoc == mamon && s.MaNamHoc == namhoc && s.MaKhoi == makhoi).Select(s=>s.MaTKBKhoi).FirstOrDefault().ToString();
        }
        public IQueryable getTKB(string malop, string manamhoc)
        {
            return DB.CanBos.Join(
                DB.PhanGVs,
                a => a.MaCanBo,
                b => b.MaCanBo,
                (a, b) => new
                {
                    a.MaCanBo,
                    a.HoTen,
                    b.MaPhanLop,
                    b.MaTKBKhoi,
                    b.MaTKB
                }
                ).Join(
                DB.PhanLops,
                a => a.MaPhanLop,
                b => b.MaPhanLop,
                (a, b) => new
                {
                    a.MaCanBo,
                    a.HoTen,
                    a.MaTKBKhoi,
                    b.MaNamHoc,
                    b.MaLop,
                    a.MaTKB
                }
                ).Join(
                DB.TKBKhois,
                a => a.MaTKBKhoi,
                b => b.MaTKBKhoi,
                (a, b) => new
                {
                    a.MaCanBo,
                    a.HoTen,
                    a.MaNamHoc,
                    a.MaLop,
                    a.MaTKB,
                    b.MaMonHoc
                }
                ).Join(
                DB.MonHocs,
                a => a.MaMonHoc,
                b => b.MaMonHoc,
                (a, b) => new
                {
                    a.MaCanBo,
                    a.HoTen,
                    a.MaNamHoc,
                    a.MaLop,
                    a.MaTKB,
                    b.TenMon
                }
                ).Where(s => s.MaNamHoc == manamhoc && s.MaLop == malop).AsQueryable();
                
                
        }
        public string Insert(PhanGVDTO phangv)
        {
            try
            {
                PhanGV gv = new PhanGV();
                gv.MaTKB = (id++).ToString();
                gv.MaTKBKhoi = phangv.MaTKBKhoi;
                gv.MaPhanLop = phangv.MaPhanLop;
                gv.MaCanBo = phangv.MaCanBo;

                DB.PhanGVs.InsertOnSubmit(gv);
                DB.SubmitChanges();
                return "t";
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }
        public bool Update(PhanGVDTO phangv)
        {
            try
            {
                PhanGV gv = DB.PhanGVs.Where(s => s.MaTKB == phangv.MaTKB).FirstOrDefault();
                gv.MaTKBKhoi = phangv.MaTKBKhoi;
                gv.MaPhanLop = phangv.MaPhanLop;
                gv.MaCanBo = phangv.MaCanBo;

                DB.SubmitChanges();
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        //lay maTKB
        public string GetMaTKB(string malop, string manamhoc)
        {
            var kq = DB.PhanGVs.Join(
                DB.PhanLops,
                a => a.MaPhanLop,
                b => b.MaPhanLop,
                (a, b) => new
                {
                    MaTKB = a.MaTKB,
                    MaLop = b.MaLop,
                    MaNamHoc = b.MaNamHoc,
                }
                ).Where(bang => bang.MaLop == malop && bang.MaNamHoc == manamhoc).FirstOrDefault();
            string matkb = kq.MaTKB;
            return matkb;
        }
    }
}
